# only support python version >= 3.6

import datetime
import pymysql
import json
import prettytable as pt
import pyperclip as pc
import sqlparse as sp

startsymbol = r'▶'
endsymbol = r'■'


# class DataEncoder(json.JSONDecoder):
#     def default(self, obj):
#         if isinstance(obj, datetime.date):
#             return obj.strftime("%Y-%m-%d")
#         elif isinstance(obj, datetime.datetime):
#             return obj.strftime("%Y-%m-%d %H:%M:%S")
#         else:
#             return json.JSONEncoder.default(self, obj)

class dblink():
    def __init__(self, dbinfo):
        self.dbinfo = dbinfo
        # 连接数据库
        print(f"连接数据库：{self.dbinfo['database']}".center(50, startsymbol))
        self.db = pymysql.connect(
            host=dbinfo['host'],
            port=dbinfo['port'],
            user=dbinfo['user'],
            passwd=dbinfo['passwd'],
            database=dbinfo['database'],
            charset=dbinfo['charset']
        )
        # 使用cursor()方法创建一个游标对象
        self.cursor = self.db.cursor(cursor=pymysql.cursors.DictCursor)  # 以字典方式返回结果

    def title(self, s):
        return f"\n【{s}】"

    def select(self, sqls):  # sql执行
        print(f"{self.title('执行sql')}：\n{sqls}")
        key = "select"
        option_key = sqls[:len(key)].lower()
        if option_key != key:
            print(f"sql语句预期关键词‘{key}’，实际关键词‘{option_key}’，请仔细检查")
            return
        else:
            try:
                self.cursor.execute(sqls)  # 使用execute()方法执行SQL语句
            except Exception as e:
                print(f"{self.title('执行错误')}：{e}")
            else:
                self.data = self.cursor.fetchall()  # 使用fetall()获取全部数据
                print(f"{self.title('执行结果')}：")
                self._result()
                return self.data

    def update(self, sqls):
        print(f"{self.title('执行sql')}：\n{sqls}")
        key = "update"
        option_key = sqls[:len(key)].lower()
        if option_key != key:
            print(f"sql语句预期关键词‘{key}’，实际关键词‘{option_key}’，请仔细检查")
            return
        elif 'WHERE' not in sqls:
            print('!未指定where条件，禁止update')
            return
        else:
            self.cursor.execute(sqls)  # 使用execute()方法执行SQL语句
            self.db.commit()  # 提交执行

    def _result(self):  # 结果处理
        data = self.data
        # 数据处理
        tb = pt.PrettyTable()
        if len(data) > 0:
            tb.field_names = [k for k in data[0]]
            for i in data:
                tb.add_row([v for k, v in i.items()])
        # pc.copy(
        # ) # 复制结果至剪贴板
        print(tb)

    def __del__(self):
        # 关闭游标和数据库的连接
        self.cursor.close()
        self.db.close()
        print(f"关闭数据库：{self.dbinfo['database']}".center(50, endsymbol))


dbinfo = dict()
# 准备数据库连接信息
dbinfo.update(host="localhost", port=3306, user="root", passwd="485172", database="test", charset='utf8')

tmp = dblink(dbinfo)
# data = tmp.select("select * FROM employees limit 5")
sqls = " UPDATE employees SET emp_no=10060, birth_date='2023-10-25', first_name='test1', last_name='test2', gender='M', hire_date='1986-06-26' where emp_no=10001;"


# 格式化sql
def fmtsql(sqls):
    sqls = sqls.strip()
    sqlfmt = sp.format(sqls, encoding=None, reindent=True, keyword_case='upper')
    return sqlfmt


tmp.update(fmtsql(sqls))
data = tmp.select(fmtsql("select * from employees"))

# # 打印获取到的数据
# for i in range(len(data)):
#     for k, v in data[i].items():
#         tmp = v
#         if isinstance(v, datetime.date):
#             tmp = v.strftime("%Y-%m-%d")
#         elif isinstance(v, datetime.datetime):
#             tmp = v.strftime("%Y-%m-%d %H:%M:%S")
#         data[i][k] = tmp
#     print(data[i])
